package org.cgs.web.common;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

import javax.mail.Message;
import javax.mail.MessagingException;
import javax.mail.Session;
import javax.mail.Transport;
import javax.mail.internet.InternetAddress;
import javax.mail.internet.MimeMessage;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;

import org.apache.log4j.Logger;
import org.cgs.web.beans.School;
import org.cgs.web.beans.Student;
import org.cgs.web.database.DatabaseConnector;

import com.lowagie.text.Chunk;
import com.lowagie.text.Document;
import com.lowagie.text.DocumentException;
import com.lowagie.text.Paragraph;
import com.lowagie.text.pdf.PdfPCell;
import com.lowagie.text.pdf.PdfPTable;
import com.lowagie.text.pdf.PdfWriter;

public final class Utility
{
	private static final Logger LOGGER = Logger.getLogger(Utility.class);
	
	private Utility()
	{
	}
	
	public static PdfPTable buildTimeTable(List<String> times, String header)
	{
		PdfPTable table = new PdfPTable(6);
		PdfPCell cell = new PdfPCell(new Paragraph(header));
		cell.setColspan(6);
		table.addCell(cell);
		//
		table.addCell("");
		table.addCell("Monday");
		table.addCell("Tuesday");
		table.addCell("Wednesday");
		table.addCell("Thursday");
		table.addCell("Friday");
		//
		table.addCell("8:30-9:00AM");
		table.addCell(times.contains(Constants.MON_0830)?"X":"");
		table.addCell(times.contains(Constants.TUE_0830)?"X":"");
		table.addCell(times.contains(Constants.WED_0830)?"X":"");
		table.addCell(times.contains(Constants.THU_0830)?"X":"");
		table.addCell(times.contains(Constants.FRI_0830)?"X":"");
		//
		table.addCell("9:00-9:30AM");
		table.addCell(times.contains(Constants.MON_0900)?"X":"");
		table.addCell(times.contains(Constants.TUE_0900)?"X":"");
		table.addCell(times.contains(Constants.WED_0900)?"X":"");
		table.addCell(times.contains(Constants.THU_0900)?"X":"");
		table.addCell(times.contains(Constants.FRI_0900)?"X":"");
		//
		table.addCell("9:30-10:00AM");
		table.addCell(times.contains(Constants.MON_0930)?"X":"");
		table.addCell(times.contains(Constants.TUE_0930)?"X":"");
		table.addCell(times.contains(Constants.WED_0930)?"X":"");
		table.addCell(times.contains(Constants.THU_0930)?"X":"");
		table.addCell(times.contains(Constants.FRI_0930)?"X":"");
		//
		table.addCell("10:00-10:30AM");
		table.addCell(times.contains(Constants.MON_1000)?"X":"");
		table.addCell(times.contains(Constants.TUE_1000)?"X":"");
		table.addCell(times.contains(Constants.WED_1000)?"X":"");
		table.addCell(times.contains(Constants.THU_1000)?"X":"");
		table.addCell(times.contains(Constants.FRI_1000)?"X":"");
		//
		table.addCell("10:30-11:00AM");
		table.addCell(times.contains(Constants.MON_1030)?"X":"");
		table.addCell(times.contains(Constants.TUE_1030)?"X":"");
		table.addCell(times.contains(Constants.WED_1030)?"X":"");
		table.addCell(times.contains(Constants.THU_1030)?"X":"");
		table.addCell(times.contains(Constants.FRI_1030)?"X":"");
		//
		table.addCell("11:00-11:30AM");
		table.addCell(times.contains(Constants.MON_1100)?"X":"");
		table.addCell(times.contains(Constants.TUE_1100)?"X":"");
		table.addCell(times.contains(Constants.WED_1100)?"X":"");
		table.addCell(times.contains(Constants.THU_1100)?"X":"");
		table.addCell(times.contains(Constants.FRI_1100)?"X":"");
		//
		table.addCell("11:30-12:00PM");
		table.addCell(times.contains(Constants.MON_1130)?"X":"");
		table.addCell(times.contains(Constants.TUE_1130)?"X":"");
		table.addCell(times.contains(Constants.WED_1130)?"X":"");
		table.addCell(times.contains(Constants.THU_1130)?"X":"");
		table.addCell(times.contains(Constants.FRI_1130)?"X":"");
		//
		table.addCell("12:00-12:30PM");
		table.addCell(times.contains(Constants.MON_1200)?"X":"");
		table.addCell(times.contains(Constants.TUE_1200)?"X":"");
		table.addCell(times.contains(Constants.WED_1200)?"X":"");
		table.addCell(times.contains(Constants.THU_1200)?"X":"");
		table.addCell(times.contains(Constants.FRI_1200)?"X":"");
		//
		table.addCell("12:30-1:00PM");
		table.addCell(times.contains(Constants.MON_1230)?"X":"");
		table.addCell(times.contains(Constants.TUE_1230)?"X":"");
		table.addCell(times.contains(Constants.WED_1230)?"X":"");
		table.addCell(times.contains(Constants.THU_1230)?"X":"");
		table.addCell(times.contains(Constants.FRI_1230)?"X":"");
		//
		table.addCell("1:00-1:30PM");
		table.addCell(times.contains(Constants.MON_0100)?"X":"");
		table.addCell(times.contains(Constants.TUE_0100)?"X":"");
		table.addCell(times.contains(Constants.WED_0100)?"X":"");
		table.addCell(times.contains(Constants.THU_0100)?"X":"");
		table.addCell(times.contains(Constants.FRI_0100)?"X":"");
		//
		table.addCell("1:30-2:00PM");
		table.addCell(times.contains(Constants.MON_0130)?"X":"");
		table.addCell(times.contains(Constants.TUE_0130)?"X":"");
		table.addCell(times.contains(Constants.WED_0130)?"X":"");
		table.addCell(times.contains(Constants.THU_0130)?"X":"");
		table.addCell(times.contains(Constants.FRI_0130)?"X":"");
		//
		table.addCell("2:00-2:30PM");
		table.addCell(times.contains(Constants.MON_0200)?"X":"");
		table.addCell(times.contains(Constants.TUE_0200)?"X":"");
		table.addCell(times.contains(Constants.WED_0200)?"X":"");
		table.addCell(times.contains(Constants.THU_0200)?"X":"");
		table.addCell(times.contains(Constants.FRI_0200)?"X":"");
		//
		table.addCell("2:30-3:00PM");
		table.addCell(times.contains(Constants.MON_0230)?"X":"");
		table.addCell(times.contains(Constants.TUE_0230)?"X":"");
		table.addCell(times.contains(Constants.WED_0230)?"X":"");
		table.addCell(times.contains(Constants.THU_0230)?"X":"");
		table.addCell(times.contains(Constants.FRI_0230)?"X":"");
		//
		table.addCell("3:00-3:30PM");
		table.addCell(times.contains(Constants.MON_0300)?"X":"");
		table.addCell(times.contains(Constants.TUE_0300)?"X":"");
		table.addCell(times.contains(Constants.WED_0300)?"X":"");
		table.addCell(times.contains(Constants.THU_0300)?"X":"");
		table.addCell(times.contains(Constants.FRI_0300)?"X":"");
		//
		table.addCell("3:30-4:00PM");
		table.addCell(times.contains(Constants.MON_0330)?"X":"");
		table.addCell(times.contains(Constants.TUE_0330)?"X":"");
		table.addCell(times.contains(Constants.WED_0330)?"X":"");
		table.addCell(times.contains(Constants.THU_0330)?"X":"");
		table.addCell(times.contains(Constants.FRI_0330)?"X":"");
		//
		return table;
	}
	
	public static void sendEmail(String host, String fromEmail, String[] recipients, String subject, String message) throws MessagingException
	{
	     //Set the host smtp address
	     Properties props = new Properties();
	     props.put("mail.smtp.host", host);

	    // create some properties and get the default Session
	    Session session = Session.getDefaultInstance(props, null);
	    session.setDebug(false);

	    // create a message
	    Message msg = new MimeMessage(session);

	    // set the from and to address
	    InternetAddress addressFrom = new InternetAddress(fromEmail);
	    msg.setFrom(addressFrom);

	    InternetAddress[] addressTo = new InternetAddress[recipients.length]; 
	    for (int i = 0; i < recipients.length; i++)
	    {
	        addressTo[i] = new InternetAddress(recipients[i]);
	    }
	    msg.setRecipients(Message.RecipientType.TO, addressTo);

	    // Setting the Subject and Content Type
	    msg.setSubject(subject);
	    msg.setContent(message, "text/plain");
	    Transport.send(msg);
	}
	
	public static List<School> getAllSchoolsData(String courseCode) throws SQLException
    {
        ArrayList<School> schools = new ArrayList<School>();

        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        PreparedStatement pstmt2 = null;
        ResultSet rs2 = null;
        try
        {
            con = DatabaseConnector.getInstance().getConnection();
            pstmt = con.prepareStatement("SELECT * FROM school WHERE courseCode=? ORDER BY name", ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
            pstmt.setString(1, courseCode);
            rs = pstmt.executeQuery();

            while (rs.next())
            {
            	School school = new School();
            	school.setCourseCode(courseCode);
				school.setName(rs.getString("name"));
				school.setContact(rs.getString("contact"));
				school.setPhone(rs.getString("phone"));
				school.setNumStudents(rs.getInt("numStudents"));
				school.setAddress(rs.getString("address"));
				school.setTimeDetails(rs.getString("details"));

				String isFar = rs.getString("isFar");
				if (isFar != null && isFar.length() > 0)
				{
					school.setFarSchoolSet(true);
					school.setFarSchool(Boolean.parseBoolean(isFar));
				}

				//
				// Get the school times
				//			
				pstmt2 = con.prepareStatement("SELECT * FROM school_times WHERE coursecode=? AND name=?");
				pstmt2.setString(1, courseCode);
				pstmt2.setString(2, school.getName());
				rs2 = pstmt2.executeQuery();

				while (rs2.next())
				{
					school.addTime(rs2.getString("time"));
				}

                // Add the school object to the list of schools
                schools.add(school);
            } // End while
        } // End try block
        catch (SQLException e)
        {
            LOGGER.error("Exception thrown retrieveing school data", e);
            throw e;
        } // End catch block
        finally
        {
            try
            {
                // Close the ResultSet
                if (rs2 != null)
                {
                    rs2.close();
                }
            }
            catch (Exception e)
            {
                LOGGER.error("Exception cleaning up result set", e);
            }

            try
            {
                // Close the PreparedStatement
                if (pstmt2 != null)
                {
                    pstmt2.close();
                }
            }
            catch (Exception e)
            {
                LOGGER.error("Exception cleaning up prepared statement", e);
            }

            try
            {
                // Close the ResultSet
                if (rs != null)
                {
                    rs.close();
                }
            }
            catch (Exception e)
            {
                LOGGER.error("Exception cleaning up result set", e);
            }

            try
            {
                // Close the PreparedStatement
                if (pstmt != null)
                {
                    pstmt.close();
                }
            }
            catch (Exception e)
            {
                LOGGER.error("Exception cleaning up prepared statement", e);
            }

            try
            {
                // Close the connection
                if (con != null)
                {
                    con.close();
                }
            }
            catch (Exception e)
            {
                LOGGER.error("Exception cleaning up connection to the database", e);
            }
        } // End finally

        return schools;
    } // End method
	
	public static List<Student> getAllStudentsData(String courseCode) throws SQLException
    {
		List<Student> students = new ArrayList<Student>();
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		PreparedStatement pstmt2 = null;
		ResultSet rs2 = null;

		try
		{
			con = DatabaseConnector.getInstance().getConnection();
			pstmt = con.prepareStatement("SELECT * FROM student WHERE courseCode=? ORDER BY name");
			pstmt.setString(1, courseCode);
			rs = pstmt.executeQuery();

			while (rs.next())
			{
				Student student = new Student();
				student.setCourseCode(courseCode);
				student.setName(rs.getString("name"));
				student.setStudentNumber(rs.getString("studentNumber"));
				student.setEmail(rs.getString("email"));
				student.setPhone(rs.getString("phone"));
				student.setLikeSubject(rs.getString("likeSubject"));
				student.setProgramAndYear(rs.getString("programAndyear"));
				student.setRecentCourse(rs.getString("recentCourse"));
				student.setTeachingAttraction(rs.getString("teachingAttraction"));
				student.setTeachingExperience(rs.getString("teachingExperience"));
				student.setTransportation(rs.getString("transportation"));
				student.setWhyInterested(rs.getString("whyInterested"));

				String hasVehicle = rs.getString("hasVehicle");
				if (hasVehicle != null && hasVehicle.length() > 0)
				{
					student.setHasVehicleSet(true);
					student.setHasVehicle(Boolean.parseBoolean(hasVehicle));
				}

				String hasTeachingExperience = rs.getString("hasTeachingExperience");
				if (hasTeachingExperience != null && hasTeachingExperience.length() > 0)
				{
					student.setHasTeachingExperienceSet(true);
					student.setHasTeachingExperience(Boolean.parseBoolean(hasTeachingExperience));
				}

				String hasSubjectKnowledge = rs.getString("hasSUbjectKnowledge");
				if (hasSubjectKnowledge != null && hasSubjectKnowledge.length() > 0)
				{
					student.setHasSubjectKnowledgeSet(true);
					student.setHasSubjectKnowledge(Boolean.parseBoolean(hasSubjectKnowledge));
				}

				//
				// Get the student times
				//

				pstmt2 = con.prepareStatement("SELECT * FROM student_times WHERE coursecode=? AND studentNumber=?");
				pstmt2.setString(1,courseCode);
				pstmt2.setString(2, student.getStudentNumber());
				rs2 = pstmt2.executeQuery();

				while (rs2.next())
				{
					student.addTime(rs2.getString("time"));
				} // end while loop

				//
				// Add this student to the list
				//
				students.add(student);
			}
		}
		catch (SQLException e)
		{
			LOGGER.error("Exception thrown while atttempting to delete schools from DB", e);
			throw e;
		}
		finally
		{
			try
			{
				// Close the ResultSet
				if (rs2 != null)
				{
					rs2.close();
				}
			}
			catch (Exception e)
			{
				LOGGER.error("Exception cleaning up result set", e);
			}

			try
			{
				// Close the ResultSet
				if (rs != null)
				{
					rs.close();
				}
			}
			catch (Exception e)
			{
				LOGGER.error("Exception cleaning up result set", e);
			}

			try
			{
				// Close the PreparedStatement
				if (pstmt2 != null)
				{
					pstmt2.close();
				}
			}
			catch (Exception e)
			{
				LOGGER.error("Exception cleaning up prepared statement", e);
			}

			try
			{
				// Close the PreparedStatement
				if (pstmt != null)
				{
					pstmt.close();
				}
			}
			catch (Exception e)
			{
				LOGGER.error("Exception cleaning up prepared statement", e);
			}

			try
			{
				// Close the connection
				if (con != null)
				{
					con.close();
				}
			}
			catch (Exception e)
			{
				LOGGER.error("Exception cleaning up connection to the database", e);
			}
		}

		return students;
    } // End method

	public static void writeToPdf(HttpServletResponse response, String msg) throws DocumentException, IOException
	{
		Document document = new Document();
		ByteArrayOutputStream baos = new ByteArrayOutputStream();
		PdfWriter.getInstance(document, baos);
		document.open();
		document.add(new Paragraph(msg));
		document.add(Chunk.NEWLINE);
		document.add(new Paragraph("The method used to generate this PDF was: "));
		document.close();
		response.setContentType("application/pdf");
		response.setContentLength(baos.size());
		response.setHeader("Expires", "0");
		response.setHeader("Cache-Control", "must-revalidate, post-check=0, pre-check=0");
		response.setHeader("Pragma", "public");

		ServletOutputStream out = response.getOutputStream();
		baos.writeTo(out);
		out.flush();
	}
}